package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.GraduateProjectCategory;
import cn.edu.sdjzu.xg.bysj.domain.OpenArea;
import cn.edu.sdjzu.xg.bysj.domain.Teacher;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class OpenAreaDao {
    private static OpenAreaDao openAreaDao = null;
    private static Collection<OpenArea> openAreas;

    public OpenAreaDao(){}

    public static OpenAreaDao getInstance() {
        if (openAreaDao == null){
            openAreaDao = new OpenAreaDao();
        }
        return openAreaDao;
    }

    public int count(Connection connection) throws SQLException {
        return CommonDao.getInstance().count(connection,"openArea");
    }

    public Collection<OpenArea> findAll(
            Connection connection, List<Condition> conditions_json, Pagination pagination)
            throws SQLException {
        //创建Department类型集合类对象，HashSet不能保证结果中元素的顺序
        Collection<OpenArea> openAreas = new HashSet<OpenArea>();

        int totalNum = OpenAreaDao.getInstance().count(connection);

        //创建查询departmentd的主语句
        StringBuilder openArea_sql = new StringBuilder("select * from openArea");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditions_json != null) {
            String whereClause = Condition.toWhereClause(conditions_json);
            openArea_sql.append(whereClause);
        }
        //如果参数不为空，为SQL语句添加LIMIT子句
        if (pagination != null){
            openArea_sql.append(pagination.toLimitClause(totalNum) + " ");
        }
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(openArea_sql.toString());
        //获得Department对象的结果集
        ResultSet resultSet = pstmt.executeQuery();
        //遍历结果集，根据元组创建对象，并添加进集合
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String area = resultSet.getString("area");
            int positionNum = resultSet.getInt("positionNum");
            int year = resultSet.getInt("year");
            String requirement = resultSet.getString("requirement");
            int teacherId = resultSet.getInt("teacher_id");
            int graduateProjectCategoryId = resultSet
                    .getInt("graduateProjectCategory_id");

            Teacher teacher = TeacherDao.getInstance().find(teacherId,connection);
            GraduateProjectCategory graduateProjectCategory =
                    GraduateProjectCategoryDao.getInstance().
                            find(graduateProjectCategoryId,connection);

            OpenArea desriedOpenAreas = new OpenArea(
                    id,area,teacher,positionNum,year,graduateProjectCategory,requirement);
            openAreas.add(desriedOpenAreas);
        }
        //关闭对象
        JdbcHelper.close(resultSet, pstmt);
        return openAreas;
    }

    public OpenArea find(Integer id, Connection connection) throws SQLException {
        OpenArea desiredOpenArea = null;
        //创建预编译语句
        String find_sql = "SELECT * FROM openArea WHERE id=?";
        //将语句放进预编译语句盒子
        PreparedStatement pstmt_find = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt_find.setInt(1,id);
        //执行语句
        ResultSet resultSet_selcet_id = pstmt_find.executeQuery();
        //将结果赋值
        if (resultSet_selcet_id.next()){
            String area = resultSet_selcet_id.getString("area");
            int positionNum = resultSet_selcet_id.getInt("positionNum");
            int year = resultSet_selcet_id.getInt("year");
            String requirement = resultSet_selcet_id.getString("requirement");
            int teacherId = resultSet_selcet_id.getInt("teacher_id");
            int graduateProjectCategoryId = resultSet_selcet_id
                    .getInt("graduateProjectCategory_id");

            Teacher teacher = TeacherDao.getInstance().find(teacherId,connection);
            GraduateProjectCategory graduateProjectCategory = GraduateProjectCategoryDao
                    .getInstance().find(graduateProjectCategoryId,connection);

            desiredOpenArea = new OpenArea(id,area,teacher,positionNum
                    ,year,graduateProjectCategory,requirement);
        }
        //关闭连接
        JdbcHelper.close(resultSet_selcet_id,pstmt_find);
        return desiredOpenArea;
    }

    public boolean update(OpenArea openArea,Connection conn) throws SQLException {
        //创建预编译语句
        String update_sql = "UPDATE openArea " +
                "SET area = ?,graduateProjectCategory_id = ? ," +
                "teacher_id = ?,positionNum = ?,year = ?,requirement = ? " +
                "WHERE id = ?";
        //将语句放进预编译语句盒子
        PreparedStatement preparedStatement = conn.prepareStatement(update_sql);
        //为预编译参数赋值
        preparedStatement.setInt(7,openArea.getId());
        preparedStatement.setString(1,openArea.getArea());
        preparedStatement.setInt(2,openArea.getGraduateProjectCategory().getId());
        preparedStatement.setInt(3,openArea.getTeacher().getId());
        preparedStatement.setInt(4,openArea.getPositionNum());
        preparedStatement.setInt(5,openArea.getYear());
        preparedStatement.setString(6,openArea.getRequirement());
        //执行语句
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement,null);
        return  rowAffected>0;
    }
    public boolean add(OpenArea openArea,Connection conn) throws SQLException {
        //创建预编译语句
        String add_sql = "INSERT INTO openArea" +
                "(area,teacher_id,graduateProjectCategory_id,positionNum,year,requirement)" +
                " VALUES (?,?,?,?,?,?)";
        //将语句放进预编译语句盒子
        PreparedStatement preparedStatement = conn.prepareStatement(add_sql);
        //为预编译参数赋值
        preparedStatement.setString(1,openArea.getArea());
        preparedStatement.setInt(2,openArea.getTeacher().getId());
        preparedStatement.setInt(3,openArea.getGraduateProjectCategory().getId());
        preparedStatement.setInt(4,openArea.getPositionNum());
        preparedStatement.setInt(5,openArea.getYear());
        preparedStatement.setString(6,openArea.getRequirement());
        //执行语句
        int rowAffected = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement,null);
        return  rowAffected>0;
    }
    public boolean delete(Integer id,Connection connection) throws SQLException {
        //获得department对象
        OpenArea openArea = OpenAreaDao.getInstance().find(id,connection);
        //调用同类另个删除方法
        return OpenAreaDao.getInstance().delete(openArea,connection);
    }
    public boolean delete(OpenArea openArea,Connection conn) throws SQLException {
        //创建sql语句
        String delete_sql = "delete from openArea where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement preparedStatement = conn.prepareStatement(delete_sql);
        //为预编译参数赋值
        preparedStatement.setInt(1, openArea.getId());
        //获取执行结果
        int affectedRowNum = preparedStatement.executeUpdate();
        //关闭预编译语句盒子
        JdbcHelper.close(preparedStatement, null);
        //判断数据库执行结果，返回相应结果
        return affectedRowNum>1;
    }
}
